# -*- coding: utf-8 -*-
# File : student.py
# Author: taoyahui
# Date : 2022/5/9

import os
import sqlite3


def quit():
    print("谢谢您的使用，再见！")
    exit(0)


def show_all():
    results = execSQL('select * from student')
    show_student(results)


def clear():
    os.system('clear')


def show_student(results):
    print('-' * 100)
    print("{0}\t{1}\t{2}\t{3}\t\t{4}\t\t{5}".format('编号', '姓名', '性别', '学号', '班级', '学院'))
    for result in results:
        print("{0}\t{1}\t{2}\t{3}\t{4}\t\t{5}".format(result[0], result[1], result[2], result[3], result[4], result[5]))
    print('-' * 100)


def execSQL(sql):
    conn = sqlite3.connect('students.db')
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        #         捕获执行SQL后的结果集
        result = cursor.fetchall()
        conn.commit()
        return result
    except BaseException as e:
        #         对指定的异常类型进行捕获，并将具体信息打印出来
        conn.rollback()
        print('错误: %s' % e)
    finally:
        conn.close()


def search():
    where_condition = '''请输入查询条件：
        1   :   按编号查询;
        2   :   按姓名查询;
        3   :   按性别查询;
        4   :   按学号查询;
        5   :   按班级查询;
        6   :   按学院查询;
        exit:   退出
查询条件： '''
    where = -1
    while True:
        #         查询的字段名所对应的数字
        condition = input(where_condition)
        if condition == 'exit':
            return
        if condition.isdigit():
            #
            where = int(condition)
            if 1 <= where <= 6:
                break
        print("输入有误，请重新输入！")
    #         查询的字段具体的值
    condition = input('请输入查询条件的值：')
    conditions = ['id', 'name', 'gender', 'student_id', 'class_name', 'college_name']
    results = execSQL("select * from student where %s = '%s'" % (conditions[where - 1], condition))
    if results:
        show_student(results)
    else:
        print("学生未找到！\n")


def change():
    show_all()
    student_id = input("当前只支持根据学生ID进行修改，请输入要修改的学生ID：")
    field_hint = '''请输入要修改的字段：
        1   :   学生姓名
        2   :   学生性别
        3   :   学生学号
        4   :   学生班级
        5   :   学生学院
        exit:   退出
要修改的字段是：'''
    field_number = -1
    while (True):
        field = input(field_hint)
        if (field == 'exit'):
            return
        if (field.isdigit()):
            field_number = int(field)
            if (field_number >= 1 and field_number <= 5):
                break
        print("输入有误，请重新输入！")
    value = input("请输入要修改的值：")
    conditions = ['id', 'name', 'gender', 'student_id', 'class_name', 'college_name']
    execSQL("update student set %s = '%s' where id = %s" % (conditions[field_number], value, student_id))
    print("修改成功！")
    results = execSQL("select * from student where id = %s" % student_id)
    if results:
        show_student(results)


def delete():
    show_all()
    student_id = input("当前只支持根据学生ID删除学生，请输入要删除的学生ID：")
    execSQL('delete from student where id = %s' % student_id)
    print('删除成功！')
    show_all()


def add():
    name = input("学生姓名：")
    gender = input("性别（男，女）：")
    student_id = input("学生ID：")
    class_name = input("班级名：")
    college_name = input("学院名：")
    execSQL("insert into student values(null,'%s','%s','%s','%s','%s')" % (
        name, gender, student_id, class_name, college_name))
    print("插入成功！")
    results = execSQL("select * from student where student_id = '%s'" % student_id)
    if results:
        show_student(results)


def show_help():
    help_str = '''
    show        :   查看所有学生；
    search      :   按条件筛选学生；
    add         :   添加学生；
    change      :   修改学生信息；
    delete      ：  删除指定学生；
    help        :   打印帮助信息；
    clear       ：  清屏；
    quit        ：  退出；
    exit        ：  退出；
    '''
    print(help_str)


def main_menu():
    menu = '''欢迎使用学生信息管理系统（版本号 v1.0）
请在>>之后输入指令（help打印帮助）

'''
    print(menu)
    command = input(">> ")
    return command


def main():
    commands = {
        'quit': quit,
        'exit': quit,
        'clear': clear,
        'show': show_all,
        'search': search,
        'change': change,
        'delete': delete,
        'help': show_help,
        'add': add,
    }

    while True:
        command = main_menu()
        if not command in commands:
            print("指令未找到，请重新输入！")
            show_help()
        else:
            (commands.get(command))()


if __name__ == '__main__':
    main()